from time import time, sleep

import pymysql
from pymysql import connect

from utils.config import Config
from utils.logger import Logger

logger = Logger('MySQLHelper').getlogger()


class MySQLHelper():

    def __init__(self):
        self.config = Config('./conf/config.ini')

        self.user = self.config.read_value('mysql', 'user')
        self.password = self.config.read_value('mysql', 'password')
        self.host = self.config.read_value('mysql', 'host')
        self.database = self.config.read_value('mysql', 'database')
        self.port = int(self.config.read_value('mysql', 'port'))
        self.charset = self.config.read_value('mysql', 'charset')

    def get_db(self):
        db = pymysql.connect(user=self.user, password=self.password, host=self.host, database=self.database, port=self.port,
                     charset=self.charset)
        return db

    def get_cursor(self):
        db = self.get_db()
        cursor = db.cursor()
        return cursor

    def select(self, sql, flag='all', number=2):
        cursor = self.get_cursor()
        cursor.execute(sql)

        # logger.info(f'sql为：{sql}')
        try:
            if flag == 'all':
                result = cursor.fetchall()
            elif flag == 'one':
                result = cursor.fetchone()
            elif flag == 'many':
                result = cursor.fetchmany(number)
            else:
                pass
            return result
        except Exception:
            logger.info('只能填one,all,many')

        self.close()

    # 不知道为什么就是不执行(估计self.get_db().commit()这个是第二次数据库连接了)
    # def dml(self, sql):
    #     cursor = self.get_cursor()
    #     cursor.execute(sql)
    #     logger.info(f'sql为：{sql}')
    #     count=cursor.rowcount
    #     self.get_db().commit()
    #     self.close()
    #     return count

    def dml(self, sql):
        # 打开数据库连接
        db=self.get_db()
        # 使用cursor()方法创建一个游标对象cursor
        cursor = db.cursor()
        cursor.execute(sql)  # 执行sql语句
        db.commit()  # 执行update操作时需要写这个，否则就会更新不成功
        count=cursor.rowcount
        self.close()
        return count

    def close(self):
        self.get_cursor().close()
        self.get_db().close()


# mysql = MySQLHelper()
# sql = "UPDATE iot_device SET extra_property_json = \"[]\", enter_status = \"0\" WHERE device_id = \"a03d605709f6436e918f800cd872ef3e\""
# count = mysql.dml(sql)
# print(count)
#
# sql = "select extra_property_json,enter_status from iot_device where device_id = \"a03d605709f6436e918f800cd872ef3e\""
# result = mysql.select(sql)
# print(result)

# sql="INSERT INTO `tw-tunnel`.tunnel_peak_trend (peak_trend_id, tunnel_direction, hour_period_begin, hour_period_end, peak_type, count_date, del_flag, create_time, update_time, tenant_id) VALUES('081c638e8e204ead93dd1e622ffad95c', 2, '2024-05-15 17:10:00', '2024-05-15 18:14:25', 1, '2024-05-15 02:00:00', '0', '2024-04-14 02:00:00', '2024-05-16 13:43:06', 'T1000')"
# count = mysql.dml(sql)
# print(count)
